package com.archerdu.demo.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.archerdu.demo.ReflectDemo;

public class DBInfo {

    private String     className;
    private String     url;
    private String     user;
    private String     password;
    private Connection conn;

    public static void main(String[] args) {
        DBInfo db = new DBInfo("com.mysql.jdbc.Driver",
                "jdbc:mysql://localhost:3306/web?useUnicode=true&amp;characterEncoding=UTF-8", "root", "123456");
        Connection conn = db.getConnection();
        try {
            System.out.println(conn.getCatalog());
            System.out.println(conn.getClientInfo());
            System.out.println(conn.getMetaData());
            System.out.println(conn.getTypeMap());
            DatabaseMetaData md = conn.getMetaData();
            System.out.println();
            // System.out.println(ReflectDemo.getProperties(md));

            // 获取数据库名
            ResultSet catalogs = md.getCatalogs();
            while (catalogs.next()) {
                System.out.println("------Catalog----------");
                String temp = catalogs.getString(1);
                System.out.println(temp);
                // 获取表名
                if (temp.equals("web")) {
                    System.out.println("------tables----------");
                    List<TableInfo> tList = db.getTables(temp);
                    for(TableInfo ti: tList){
                        System.out.print(ReflectDemo.getProperties(ti));
                        List<ColumnInfo> list = db.getColumns(temp, ti.getName());
                        // 数据库列信息
                        for (ColumnInfo ci : list) {
                            System.out.print(ReflectDemo.getProperties(ci));
                        }
                        System.out.println();
                        System.out.println();
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 初始化主要参数
     * 
     * @param className
     * @param url
     * @param user
     * @param password
     */
    public DBInfo(String className, String url, String user, String password) {
        this.className = className;
        this.url = url;
        this.user = user;
        this.password = password;
    }

    /**
     * 取得链接
     * 
     * @return
     */
    public Connection getConnection() {
        if (conn == null) {
            try {
                Class.forName(className);
                conn = DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }

    /**
     * 获取表信息
     * 
     * @param catalog
     * @param table
     * @return
     */
    public List<ColumnInfo> getColumns(String catalog, String table) {
        List<ColumnInfo> list = new ArrayList<ColumnInfo>();
        if (null != catalog && null != table) {
            try {
                DatabaseMetaData md = getConnection().getMetaData();
                ResultSet columns = md.getColumns(catalog, null, table, null);
                while (columns.next()) {
                    ColumnInfo ci = new ColumnInfo();
                    ci.setName(columns.getString(4));
                    list.add(ci);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list;
    }

    /**
     * 获取表信息
     * 
     * @param catalog
     * @return
     */
    public List<TableInfo> getTables(String catalog) {
        List<TableInfo> list = new ArrayList<TableInfo>();
        if (null != catalog) {
            DatabaseMetaData md;
            try {
                md = getConnection().getMetaData();
                ResultSet tables = md.getTables(catalog, null, null, null);
                while (tables.next()) {
                    TableInfo ti = new TableInfo();
                    ti.setCatalog(tables.getString(1));
                    ti.setName(tables.getString(3));
                    list.add(ti);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
}
